/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import controller.loaiDanhMuc;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.Locale;
import model.Chitietdaugia;
import model.Danhmuc;
import model.DanhmucNhasanxuat;
import model.Hinhanh;
import model.Loaidanhmuc;
import model.Loaidaugia;
import model.Sanpham;
import model.Tinhtrangdaugia;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import util.HibernateUtil;

/**
 *
 * @author Tran Manh Linh
 */
public class SanphamDAO {

    public static ArrayList<Sanpham> getAllProducts() {
        ArrayList<Sanpham> listAllProducts = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 order by sp.thoiDiemKetThuc";
            trans.begin();
            Query query = ss.createQuery(hql);
            listAllProducts = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return listAllProducts;
        } catch (Exception ex) {
            listAllProducts = null;
        } finally {
            ssFac.close();
            return listAllProducts;
        }
    }

    public static ArrayList<Sanpham> getProducts(Danhmuc maDanhMuc) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmnsx where dmnsx.danhmuc=:maDanhMuc"
                    + " and sp.danhmucNhasanxuat = dmnsx.maDanhMucNhaSanXuat and sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai=0";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("maDanhMuc", maDanhMuc);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }

    public static ArrayList<Sanpham> getProducts(Danhmuc maDanhMuc, float giaDau, float giaCuoi, int kieuGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmnsx where dmnsx.danhmuc=:maDanhMuc"
                    + " and sp.danhmucNhasanxuat = dmnsx.maDanhMucNhaSanXuat and sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai=0";
            if (kieuGia == 1) {
                hql = hql + " and sp.giaKhoiDiem >=:giaDau and sp.giaKhoiDiem<=:giaCuoi";
            } else {
                hql = hql + " and sp.giaBanLien >=:giaDau and sp.giaBanLien<=:giaCuoi";
            }
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("maDanhMuc", maDanhMuc);
            query.setFloat("giaDau", giaDau);
            query.setFloat("giaCuoi", giaCuoi);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }
    //Y như hàm trên nhưng có xét thêm loại đấu giá

    public static ArrayList<Sanpham> getProducts(Danhmuc maDanhMuc, Loaidaugia loaiDauGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmnsx where dmnsx.danhmuc=:maDanhMuc"
                    + " and sp.danhmucNhasanxuat = dmnsx.maDanhMucNhaSanXuat and sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai=0 and sp.loaidaugia=:loaiDauGia";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("maDanhMuc", maDanhMuc);
            query.setEntity("loaiDauGia", loaiDauGia);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }
    //Y như hàm trên nhưng có xét thêm khoảng cách giá
    //kieuGia:1 là kieu gia khoi diem, 2 la kieu gia mua ngay

    public static ArrayList<Sanpham> getProducts(Danhmuc maDanhMuc, Loaidaugia loaiDauGia, float giaDau, float giaCuoi, int kieuGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmnsx where dmnsx.danhmuc=:maDanhMuc"
                    + " and sp.danhmucNhasanxuat = dmnsx.maDanhMucNhaSanXuat and sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai=0 and sp.loaidaugia=:loaiDauGia";
            if (kieuGia == 1) {
                hql = hql + " and sp.giaKhoiDiem >=:giaDau and sp.giaKhoiDiem<=:giaCuoi";
            } else {
                hql = hql + " and sp.giaBanLien >=:giaDau and sp.giaBanLien<=:giaCuoi";
            }
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("maDanhMuc", maDanhMuc);
            query.setEntity("loaiDauGia", loaiDauGia);
            query.setFloat("giaDau", giaDau);
            query.setFloat("giaCuoi", giaCuoi);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }

    //Lay san pham theo danh muc và nhà san xuat
    public static ArrayList<Sanpham> getProducts(DanhmucNhasanxuat dmNsx) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "from Sanpham sp where sp.danhmucNhasanxuat=:dmNsx and sp.trangThai=0"
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("dmNsx", dmNsx);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }

    public static ArrayList<Sanpham> getProducts(DanhmucNhasanxuat dmNsx, float giaDau, float giaCuoi, int kieuGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "from Sanpham sp where sp.danhmucNhasanxuat=:dmNsx and sp.trangThai=0"
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()";
            if (kieuGia == 1) {
                hql = hql + " and sp.giaKhoiDiem >=:giaDau and sp.giaKhoiDiem<=:giaCuoi";
            } else {
                hql = hql + " and sp.giaBanLien >=:giaDau and sp.giaBanLien<=:giaCuoi";
            }
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("dmNsx", dmNsx);
            query.setFloat("giaDau", giaDau);
            query.setFloat("giaCuoi", giaCuoi);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }
    //Y như hàm trên nhưng có xét thêm loại đấu giá

    public static ArrayList<Sanpham> getProducts(DanhmucNhasanxuat dmNsx, Loaidaugia loaiDauGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "from Sanpham sp where sp.danhmucNhasanxuat=:dmNsx and sp.trangThai=0"
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.loaidaugia=:loaiDauGia";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("dmNsx", dmNsx);
            query.setEntity("loaiDauGia", loaiDauGia);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }

    public static ArrayList<Sanpham> getProducts(DanhmucNhasanxuat dmNsx, Loaidaugia loaiDauGia, float giaDau, float giaCuoi, int kieuGia) {
        ArrayList<Sanpham> list = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        try {
            Transaction trans = ss.getTransaction();
            String hql = "from Sanpham sp where sp.danhmucNhasanxuat=:dmNsx and sp.trangThai=0"
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.loaidaugia=:loaiDauGia";
            if (kieuGia == 1) {
                hql = hql + " and sp.giaKhoiDiem >=:giaDau and sp.giaKhoiDiem<=:giaCuoi";
            } else {
                hql = hql + " and sp.giaBanLien >=:giaDau and sp.giaBanLien<=:giaCuoi";
            }
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("dmNsx", dmNsx);
            query.setEntity("loaiDauGia", loaiDauGia);
            query.setFloat("giaDau", giaDau);
            query.setFloat("giaCuoi", giaCuoi);
            list = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
            return list;
        } catch (Exception ex) {
            list = null;
        } finally {
            ssFac.close();
            return list;
        }
    }
    //

    public static Sanpham getProduct(String masp) {
        Sanpham sp = new Sanpham();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp where sp.maSanPham =:masp";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("masp", masp);
            sp = (Sanpham) query.uniqueResult();
            ss.flush();
            trans.commit();
            return sp;
        } catch (Exception e) {
            ssFac.close();
            return null;
        }
    }

    public static ArrayList<Sanpham> getClosingBidProduct() {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0 order by sp.thoiDiemKetThuc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setMaxResults(16);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<String> getUserBidMaSp(String username) {
        ArrayList<String> listProductsFinded = new ArrayList<String>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select distinct ctdg.sanpham.maSanPham from Chitietdaugia ctdg"
                    + " where ctdg.thanhvien.tenDangNhap = :username and ctdg.sanpham.thoiDiemKetThuc < current_timestamp() and ctdg.sanpham.trangThai = 0";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("username", username);
            listProductsFinded = (ArrayList<String>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static Float getUserbidThisProductHighest(String username, String masp) {
        Float kq = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select max(ctdg.giaDau) from Chitietdaugia ctdg"
                    + " where ctdg.thanhvien.tenDangNhap = :username and ctdg.sanpham.maSanPham = :masp and ctdg.sanpham.trangThai = 0";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("username", username);
            query.setString("masp", masp);
            kq = (Float) query.uniqueResult();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            kq = null;
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return kq;
        }
    }

    public static ArrayList<Sanpham> getUserBidWinProduct(String username) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        ArrayList<String> listMasp = getUserBidMaSp(username);
        try {
            for (int i = 0; i < listMasp.size(); i++) {
                if (getUserbidThisProductHighest(username, listMasp.get(i)).equals(getHighestPriceBid(listMasp.get(i)))) {
                    listProductsFinded.add(getProduct(listMasp.get(i)));
                }
            }
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getNearestProduct() {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0 order by sp.thoiDiemBatDau desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setMaxResults(16);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<String> getTopBid() {
        ArrayList<Object[]> listObjectTemp = new ArrayList<Object[]>();
        ArrayList<String> listProductsFinded = new ArrayList<String>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select ctdg.sanpham.maSanPham , count(ctdg.maChiTietDauGia) from Chitietdaugia ctdg"
                    + " where ctdg.sanpham.thoiDiemKetThuc >= current_timestamp() and ctdg.sanpham.trangThai = 0"
                    + " group by ctdg.sanpham.maSanPham order by 2 desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setMaxResults(2);
            listObjectTemp = (ArrayList<Object[]>) query.list();
            ss.flush();
            trans.commit();
            for (int i = 0; i < listObjectTemp.size(); i++) {
                listProductsFinded.add(listObjectTemp.get(i)[0].toString());
                listProductsFinded.add(listObjectTemp.get(i)[1].toString());
            }
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static Float getHighestPriceBid(String masp) {
        Float kq = 0f;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select max(ctdg.giaDau) from Chitietdaugia ctdg where ctdg.sanpham.maSanPham = :masp";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("masp", masp);
            kq = (Float) query.uniqueResult();
            ss.flush();
            trans.commit();
            if (kq == null) {
                kq = 0f;
            }
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return kq;
        }
    }

    public static Float getGiaKhoiDiem(String masp) {
        Float kq = 0f;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp.giaKhoiDiem from Sanpham sp where sp.maSanPham = :masp";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("masp", masp);
            kq = (Float) query.uniqueResult();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return kq;
        }
    }

    public static Boolean insertNewPriceBid(String masp, Float newbid, String tenDangNhap) {
        Boolean kq = false;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            if (newbid > getHighestPriceBid(masp) && newbid > getGiaKhoiDiem(masp)) {
                Chitietdaugia ct = new Chitietdaugia();
                trans.begin();
                ct.setGiaDau(newbid);
                ct.setSanpham(getProduct(masp));
                ct.setThanhvien(ThanhVienDAO.getUserById(tenDangNhap));
                ct.setThoiDiemDauGia(new java.util.Date());
                ss.merge(ct);
                ss.flush();
                trans.commit();
                kq = true;
            }
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
            kq = false;
        } finally {
            ssFac.close();
            return kq;
        }
    }

    public static String getNowDate() {
        Date now = new java.util.Date();
        SimpleDateFormat sdf = new SimpleDateFormat("dddd, dd MMMM yyyy HH:mm:ss");
        String now2 = sdf.format(now);
        return now2;
    }

    public static ArrayList<Chitietdaugia> getDSchiTietDauGia(String masp) {
        ArrayList<Chitietdaugia> dsctdg = new ArrayList<Chitietdaugia>();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction trans = session.getTransaction();//session.beginTransaction();     
        try {
            trans.begin();
            String hql = "from Chitietdaugia ctdg where ctdg.sanpham.maSanPham = :masp order by ctdg.giaDau desc";
            Query query = session.createQuery(hql);
            query.setString("masp", masp);
            dsctdg = (ArrayList<Chitietdaugia>) query.list();
            trans.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return dsctdg;
    }

    public static String customFormat(String pattern, double value) {
        DecimalFormat myFormatter = new DecimalFormat(pattern);
        String output = myFormatter.format(value);
        return output;
    }
    //Lay danh sach hinh anh cua 1 san pham

    public static ArrayList<ArrayList<Hinhanh>> getAllImages(ArrayList<Sanpham> productsList) {
        ArrayList<ArrayList<Hinhanh>> listImageList = null;
        try {
            if (productsList.size() == 0) {
                return listImageList;
            }
            listImageList = new ArrayList<ArrayList<Hinhanh>>();
            for (int i = 0; i < productsList.size(); i++) {
                Sanpham sp = productsList.get(i);
                ArrayList<Hinhanh> imagesList = null;
                if (sp.getHinhanhs().size() > 0) {
                    Iterator<Hinhanh> ite = sp.getHinhanhs().iterator();
                    imagesList = new ArrayList<Hinhanh>();
                    while (ite.hasNext()) {
                        imagesList.add(ite.next());
                    }
                }
                listImageList.add(imagesList);
            }
        } catch (Exception ex) {
            listImageList = null;
            ex.printStackTrace();
        }
        return listImageList;
    }

    public static ArrayList<Hinhanh> getImagesOfProduct(Sanpham product) {
        ArrayList<Hinhanh> imagesList = null;
        if (product.getHinhanhs().size() > 0) {
            imagesList = new ArrayList<Hinhanh>();
            Iterator<Hinhanh> ite = product.getHinhanhs().iterator();
            while (ite.hasNext()) {
                imagesList.add(ite.next());
            }
        }
        return imagesList;
    }

    //Tinh so luong trang
    public static int tinhSoLuongTrang(int soLuongSanPham, int soTuLanhTrenTrang) {
        int soTrang;
        soTrang = soLuongSanPham / soTuLanhTrenTrang;
        if (soLuongSanPham % soTuLanhTrenTrang != 0) {
            soTrang++;
        }
        return soTrang;
    }
    //Xoa tạm sản phẩm

    public static boolean delete(String maSanPham) {
        boolean result = false;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session session = ssFac.getCurrentSession();
        Transaction trans = session.getTransaction();
        String hql = "update Sanpham sp set sp.trangThai=1 where sp.maSanPham=:maSanPham";
        Query query = session.createQuery(hql);
        query.setString("maSanPham", maSanPham);
        try {
            trans.begin();
            int n = query.executeUpdate();
            if (n == 1) {
                result = true;
            }
            trans.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            result = false;
        }
        return result;
    }

    public static boolean insertOrUpdate(Sanpham sanPham) {
        boolean result = false;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session session = ssFac.getCurrentSession();
        Transaction trans = session.getTransaction();
        try {
            trans.begin();
            session.merge(sanPham);
            result = true;
            trans.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            result = false;
        }
        return result;
    }

    public static String generateMaSp() {
        int kq = 0;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        Sanpham sp = new Sanpham();
        try {
            String hql = "Select count(*) from Sanpham";
            trans.begin();
            Query query = ss.createQuery(hql);
            kq = Integer.parseInt(query.uniqueResult().toString()) + 1;
            while (true) {
                hql = "Select sp from Sanpham sp where sp.maSanPham = :masp";
                query = ss.createQuery(hql);
                query.setString("masp", "sp" + Integer.toString(kq));
                sp = (Sanpham) query.uniqueResult();
                if (sp != null) {
                    kq += 1;
                } else {
                    break;
                }
            }
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return "sp" + Integer.toString(kq);
        }
    }
    /*
    public static boolean update(Sanpham sanPham) {
    boolean result = false;
    SessionFactory ssFac = HibernateUtil.getSessionFactory();
    Session session = ssFac.getCurrentSession();
    Transaction trans = session.getTransaction();
    try {
    String hql = "update Sanpham sp "
    + " set sp.tenSanPham=:tenSanPham, sp.moTa=:moTa, sp.giaKhoiDiem=:giaKhoiDiem,"
    + " sp.giaBanLien=:giaBanLien,sp.thoiDiemBatDau=:thoiDiemBatDau, sp.thoiDiemKetThuc=:thoiDiemKetThuc,"
    + " sp.tinhTrang = 1, sp.danhmucNhasanxuat=:danhmucNhasanxuat, sp.loaidaugia=:loaidaugia,"
    + " sp.trangThai=:trangThai where sp.maSanPham=:maSanPham";
    Query query = session.createQuery(hql);
    query.setString("maSanPham", sanPham.getMaSanPham());
    query.setString("tenSanPham", sanPham.getTenSanPham());
    query.setString("moTa", sanPham.getMoTa());
    query.setFloat("giaKhoiDiem", sanPham.getGiaKhoiDiem());
    query.setFloat("giaBanLien", sanPham.getGiaBanLien());
    query.setDate("thoiDiemBatDau", sanPham.getThoiDiemBatDau());
    query.setDate("thoiDiemKetThuc", sanPham.getThoiDiemKetThuc());
    query.setEntity("danhmucNhasanxuat", sanPham.getDanhmucNhasanxuat());
    query.setEntity("loaidaugia", sanPham.getLoaidaugia());
    query.setBoolean("trangThai", sanPham.getTrangThai());
    
    trans.begin();
    int n = query.executeUpdate();
    if (n == 1) {
    result = true;
    }
    trans.commit();
    } catch (Exception ex) {
    ex.printStackTrace();
    result = false;
    }
    return result;
    }
     */

    public static ArrayList<Sanpham> quickSearch(String tuKhoa) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and (sp.tenSanPham like :str or sp.maSanPham like :str or sp.danhmucNhasanxuat.nhasanxuat.tenNhaSanXuat like :str)"
                    + " and sp.trangThai = 0 order by sp.tenSanPham";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setString("str", "%" + tuKhoa + "%");
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }
    //Lay san pham sap hết hạn

    public static ArrayList<Sanpham> getClosingBidProducts(int max) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 order by sp.thoiDiemKetThuc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getClosingBidProducts(int max, String tuKhoa) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and (sp.tenSanPham like :str or sp.maSanPham like :str or sp.danhmucNhasanxuat.nhasanxuat.tenNhaSanXuat like :str)"
                    + " and sp.trangThai = 0 order by sp.thoiDiemKetThuc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            query.setString("str", "%" + tuKhoa + "%");
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getClosingBidProducts(Loaidanhmuc loaiDanhMuc, int max) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmNsx, Danhmuc dm "
                    + " where sp.danhmucNhasanxuat = dmNsx.maDanhMucNhaSanXuat and dmNsx.danhmuc=dm.maDanhMuc and dm.loaidanhmuc=:loaidanhmuc "
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0 order by sp.thoiDiemKetThuc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("loaidanhmuc", loaiDanhMuc);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getDollarProduct(int max, int dollar) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 and sp.giaKhoiDiem < :dollar"
                    + " order by sp.thoiDiemKetThuc desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setInteger("dollar", dollar);
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getDollarProduct(int max, int dollar, String tuKhoa) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 and sp.giaKhoiDiem < :dollar"
                    + " and (sp.tenSanPham like :str or sp.maSanPham like :str or sp.danhmucNhasanxuat.nhasanxuat.tenNhaSanXuat like :str)"
                    + " order by sp.thoiDiemKetThuc desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setInteger("dollar", dollar);
            query.setMaxResults(max);
            query.setString("str", "%" + tuKhoa + "%");
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getNewArrivals(int max) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0"
                    + " order by sp.thoiDiemKetThuc desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getNewArrivals(int max, String tuKhoa) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "from Sanpham sp"
                    + " where sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0"
                    + " and (sp.tenSanPham like :str or sp.maSanPham like :str or sp.danhmucNhasanxuat.nhasanxuat.tenNhaSanXuat like :str)"
                    + " order by sp.thoiDiemKetThuc desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            query.setString("str", "%" + tuKhoa + "%");
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Sanpham> getNewArrivals(Loaidanhmuc loaiDanhMuc, int max) {
        ArrayList<Sanpham> listProductsFinded = new ArrayList<Sanpham>();
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp from Sanpham sp, DanhmucNhasanxuat dmNsx, Danhmuc dm "
                    + " where sp.danhmucNhasanxuat = dmNsx.maDanhMucNhaSanXuat and dmNsx.danhmuc=dm.maDanhMuc"
                    + " and dm.loaidanhmuc=:loaidanhmuc and sp.thoiDiemBatDau <= current_timestamp()"
                    + " and sp.thoiDiemKetThuc >= current_timestamp() and sp.trangThai = 0"
                    + " order by sp.thoiDiemKetThuc desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("loaidanhmuc", loaiDanhMuc);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Sanpham>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Object[]> getPopularProducts(int max) {

        ArrayList<Object[]> listProductsFinded = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp,count(ctdg.maChiTietDauGia)"
                    + " from Sanpham sp, Chitietdaugia ctdg "
                    + " where ctdg.sanpham=sp.maSanPham "
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 group by sp.maSanPham order by 2 desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Object[]>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Object[]> getPopularProducts(int max, String tuKhoa) {

        ArrayList<Object[]> listProductsFinded = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp,count(ctdg.maChiTietDauGia)"
                    + " from Sanpham sp, Chitietdaugia ctdg "
                    + " where ctdg.sanpham=sp.maSanPham "
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and (sp.tenSanPham like :str or sp.maSanPham like :str or sp.danhmucNhasanxuat.nhasanxuat.tenNhaSanXuat like :str)"
                    + " and sp.trangThai = 0 group by sp.maSanPham order by 2 desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            query.setString("str", "%" + tuKhoa + "%");
            listProductsFinded = (ArrayList<Object[]>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static ArrayList<Object[]> getPopularProducts(Loaidanhmuc loaiDanhMuc, int max) {

        ArrayList<Object[]> listProductsFinded = null;
        SessionFactory ssFac = HibernateUtil.getSessionFactory();
        Session ss = ssFac.openSession();
        Transaction trans = ss.getTransaction();
        try {
            String hql = "select sp,count(ctdg.maChiTietDauGia)"
                    + " from Sanpham sp, DanhmucNhasanxuat dmNsx, Danhmuc dm,Chitietdaugia ctdg "
                    + " where ctdg.sanpham=sp.maSanPham and sp.danhmucNhasanxuat = dmNsx.maDanhMucNhaSanXuat"
                    + " and dmNsx.danhmuc=dm.maDanhMuc and dm.loaidanhmuc=:loaidanhmuc "
                    + " and sp.thoiDiemBatDau <= current_timestamp() and sp.thoiDiemKetThuc >= current_timestamp()"
                    + " and sp.trangThai = 0 group by sp.maSanPham order by 2 desc";
            trans.begin();
            Query query = ss.createQuery(hql);
            query.setEntity("loaidanhmuc", loaiDanhMuc);
            if (max < 1) {
                max = 1;
            }
            query.setMaxResults(max);
            listProductsFinded = (ArrayList<Object[]>) query.list();
            ss.flush();
            trans.commit();
        } catch (HibernateException ex) {
            System.out.println(ex.getMessage());
        } finally {
            ssFac.close();
            return listProductsFinded;
        }
    }

    public static String chuanHoaTenSp(String tensp) {
        if (tensp.length() > 35) {
            tensp = tensp.substring(0, 35);
        }
        return tensp;
    }
}
